package com.cms.biz.business.impl;

import java.sql.ResultSet;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cms.biz.business.CMSGeneralContractServiceMgr;
import com.common.util.EMPSQLUtil;
import com.efounder.builder.base.data.DataSetUtils;
import com.efounder.builder.base.data.EFDataSet;
import com.efounder.builder.base.data.EFRowSet;
import com.efounder.eai.data.JParamObject;
import com.efounder.sql.JConnection;
import com.efounder.sql.JStatement;
import com.framework.sys.business.AbstractBusinessObjectServiceMgr;

@Transactional(rollbackFor = Exception.class)
@Service("CMSGeneralContractServiceMgr")
public class CMSGeneralContractMgrImpl extends AbstractBusinessObjectServiceMgr implements CMSGeneralContractServiceMgr {
	
	@Autowired
	private JdbcTemplate                    jdbcTemplate;
	
	public EFDataSet loadCMSGeneralContract(JParamObject PO) {
		JConnection    conn = null;
		JStatement     stmt = null;
		ResultSet        rs = null;
		EFDataSet   dataSet = EFDataSet.getInstance(PO.GetValueByParamName("TABLE_NAME", ""));
		EFDataSet objcolsDS = EFDataSet.getInstance("SYS_OBJCOLS");
		EFRowSet     rowset = null;
		String       strSql = " select COL_ID, COL_MC " + " from SYS_OBJCOLS" + " where OBJ_ID = '" + PO.GetValueByParamName("TABLE_NAME", "") + "' and F_STAU = '1'";
		String   TABLE_NAME = PO.GetValueByParamName("TABLE_NAME", "");
		String     sqlWhere = PO.GetValueByParamName("sqlWhere", null);
		String   sqlOrderBy = PO.GetValueByParamName("sqlOrderBy", null);
		String   sqlGroupBy = PO.GetValueByParamName("sqlGroupBy", null);
		
		try {
			conn = JConnection.getInstance(jdbcTemplate.getDataSource().getConnection());
			stmt = conn.createStatement();
			rs = stmt.executeQuery(strSql);
			objcolsDS = DataSetUtils.resultSet2DataSet(rs, objcolsDS);
			
			strSql = "select ";
			for(int i = 0; i < objcolsDS.getRowCount(); i++) {
				rowset = objcolsDS.getRowSet(i);
				strSql += TABLE_NAME + "." + rowset.getString("COL_ID", "") + " as " + rowset.getString("COL_ID", "") + ",";
			}
			strSql += " HTFKXX_V.F_HTJE as F_YFK, (HTXXZD.F_HTJE - HTFKXX_V.F_HTJE) as F_WFK"
				    + " from HTXXZD "
				    + " left join HTFKXX_V on HTFKXX_V.F_HTBH = HTXXZD.F_HTBH";
			
			if(sqlWhere != null) {
				strSql += " where " + sqlWhere;
			}
			if(sqlOrderBy != null) {
				strSql += " order by " + sqlOrderBy;
			}
			if(sqlGroupBy != null) {
				strSql += " group by " + sqlGroupBy;
			}
			
			rs = stmt.executeQuery(strSql);
			dataSet = DataSetUtils.resultSet2DataSet(rs, dataSet);
			stmt.close();
		} catch (Exception e) {
			e.printStackTrace();
			dataSet = EFDataSet.getInstance();
		} finally {
			try {
				EMPSQLUtil.closeAllResources(rs, stmt, conn);			
			} catch(Exception ce) {
				ce.printStackTrace();
			}
		}
		return dataSet;
	}

}
